package cn.chainco.cts.idaoImpl;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;

import cn.chainco.cts.idao.IPointDao;
import cn.chainco.cts.jdo.Country;
import cn.chainco.cts.jdo.Point;

public class PointDaoImpl extends GenericHibernateDaoImpl<Point, Long> implements IPointDao {
	
	public List<Point> findPointByName(String name){
		return findByProperty("name", name);
	}
	
	public List<Point> findPointDuplicate(Country country, String name, int type, long id) {
		if (id == 0) {
			return findByCriteria(Restrictions.eq("country", country), Restrictions.eq("name", name),
					Restrictions.eq("type", type));
		} else {
			return findByCriteria(Restrictions.eq("country", country), Restrictions.eq("name", name),
					Restrictions.eq("type", type), Restrictions.ne("id", id));
		}
	}
	
	/**
	 * 不区分大小写查询地点
	 * @param type 1：海港+内陆； 0：空港+内陆
	 */
	@SuppressWarnings("unchecked")
	public List<Object> findPointByName(Point point, int type) {
		if( point == null  ){
			return new ArrayList<Object>();
		}
		String hql = "select p.id from Point p where lower(p.fullname) = ? and p.type != ?";
		return this.getSession().createQuery(hql).setParameter(0, point.getFullname().toLowerCase()).setParameter(1, type).list();
	}
	
	/**
	 * 检查地名是否可以删除
	 */
	public boolean isPointCanDelete(Point point){
		String hql="select count(s.id) from Sheet s where s.departure=? or s.arrival=?";
		Object result=this.getSession().createQuery(hql).setParameter(0, point).setParameter(1, point).uniqueResult();
		if(result==null){
			return true;
		}else{
			int r=Integer.parseInt(result.toString());
			if(r>0)
				return false;
			else
				return true;
		}
	}
	
	/**
	 * 根据国家删除地名
	 */
	public void deletePointByCountry(Country country){
		String hql ="delete Point where country=?";
		this.getSession().createQuery(hql).setParameter(0, country).executeUpdate();
	}
	
	/**
	 * 此方法是将地点数据补充完整
	 * @param startIndex
	 * @param endIndex
	 */
	@SuppressWarnings("unchecked")
	public List<Point> updatePointByGroup(){
		String hql="from Point p where p.type=1";
		Query query = this.getSession().createQuery( hql );
		//query.setFirstResult(0);
		//query.setMaxResults(10000);
		return query.list();
	}
	
	/**
	 * 关键字查地点
	 * @param isPrefix
	 * @param type
	 * @param keyword
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Point> findPointByKeyword(boolean isPrefix, int type, String keyword, int records) {
		StringBuffer sb = new StringBuffer();
		sb.append("from Point p where p.type = ").append(type);
		
		if (isPrefix && type == Point.PLACE_TYPE_AIRPORT && keyword.length() == 3) {
			// 空港特殊处理
			sb.append(" and p.airCode = '").append(keyword).append("'");
			
		} else {
			if (isPrefix) {
				sb.append(" and p.fullnameNoSymbol like '").append(keyword).append("%'");
			} else {
				sb.append(" and p.fullnameNoSymbol not like '").append(keyword).append("%' and p.fullnameNoSymbol like '%").append(keyword).append("%'");
				
				// 空港特殊处理
				if (type == Point.PLACE_TYPE_AIRPORT && keyword.length() == 3) {
					sb.append(" and p.airCode != '").append(keyword).append("'");
				}
				
			}
			sb.append(" order by length(p.name) asc");
		}
		
		return this.getSession().createQuery(sb.toString()).setMaxResults(records).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Point> findPortsByLat(int type, Double latlng) {
		String hql = "from Point p where p.type = ? order by abs(? - p.latitude) asc";
		return this.getSession().createQuery(hql).setParameter(0, type).setParameter(1, latlng)
				.setMaxResults(50).list();
	}

	@SuppressWarnings("unchecked")
	public List<Point> findPortsByLng(int type, Double latlng) {
		String hql = "from Point p where p.type = ? order by abs(? - p.longitude) asc";
		return this.getSession().createQuery(hql).setParameter(0, type).setParameter(1, latlng)
				.setMaxResults(50).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Point> findNeighboringPorts(long id, int type) {
		String hql = "from Point p where p.id != ? and p.type = ?";
		return this.getSession().createQuery(hql).setParameter(0, id).setParameter(1, type).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Point> findNoLatLngPoints() {
		String hql = "from Point p where p.type = 2 and p.zoomLevel is NULL";
		return this.getSession().createQuery(hql).setMaxResults(1000).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Point> findPortsByLatLng(int type, Double[] latlng) {
		String hql = "from Point p where p.type=? and p.latitude>=? and p.latitude<=? and p.longitude>=? and p.longitude<=?";
		return this.getSession().createQuery(hql).setParameter(0, type).setParameter(1, latlng[0])
				.setParameter(2, latlng[2]).setParameter(3, latlng[1]).setParameter(4, latlng[3]).list();
	}
	
	/**
	 * 获取地点的分组标签
	 */
	@SuppressWarnings("unchecked")
	public List<String> findPointGroupNameByString(){
		String sql="select DISTINCT p.groupName from m68_point p where p.groupName is not null and p.groupName !=''";
		return this.getSession().createSQLQuery(sql).list();
	}
	
	/**
	 * 修改地点的分组状态
	 * @param id
	 */
	public void updatePointGroup(String idStr){
		String sql="update m68_point set pointGroupId=null where pointGroupId in "+idStr;
		this.getSession().createSQLQuery(sql).executeUpdate();
		sql="delete from p_pointgroup where id in "+idStr;
		this.getSession().createSQLQuery(sql).executeUpdate();
	}
	
	/**
	 * 修改地点的分组状态
	 * @param id
	 */
	public void updatePointGroup(Long id){
		String sql="update m68_point set pointGroupId=null where id = "+id;
		this.getSession().createSQLQuery(sql).executeUpdate();
	}
}
